package com.css.bjdt_core.executive;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.nutz.dao.Sqls;

import com.css.bjdt_core.cell.entity.Cell;
import com.css.bjdt_core.cell.entity.VarParam;
import com.css.bjdt_core.cell.service.CellDataService;
import com.css.bjdt_core.report.entity.RptTemplateUnitRela;
import com.css.bjdt_core.util.spring.SpringContextHolder;

public class SqlExecutor extends DefaultExecutor {

	private static Log logger = LogFactory.getLog(SqlExecutor.class);

	@Override
	public String execute(Cell cell, RptTemplateUnitRela templateUnitRela) {
		String result = "0";
		if (cell.getSqlExp() != null) {
			if (logger.isDebugEnabled()) {
				logger.debug("executeSql cell id=" + cell.getCellId());
			}
			CellDataService cellDataService = SpringContextHolder.getBean(CellDataService.class);

			Map<String, Object> params = new HashMap<String, Object>();
			params.put("dzzId", templateUnitRela.getReportUnitId());
			params.put("startTimeConditionVal", templateUnitRela.getStartTimeStr());
			params.put("endTimeConditionVal", templateUnitRela.getEndTimeStr());
			params.put("sessionDzzId", templateUnitRela.getSesssionDzzId());

			try {
				String sqlExp = cell.getSqlExp();
				String joinCond = " = dzzid_mapping.subdzzid and dzzid_mapping.dzzid = @dzzId) ";
				String inCond = " (select subdzzid from DZZID_MAPPING where dzzid = @dzzId) ";

				StringBuilder temp = convertQuerySql(sqlExp, params);
				int count = -1, i = 0;
				while (i >= 0) {
					i = temp.indexOf("$unitConditionVal", i + 1);
					count++;
				}
				if (count == 1) {
					int iwhere = temp.indexOf("where");
					if (iwhere >= 0) {
						temp.replace(iwhere, iwhere + 5, " ,DZZID_MAPPING dzzid_mapping where ");
					}
					int ijoincond = temp.indexOf("in $unitConditionVal");
					int irange = temp.lastIndexOf(" ", ijoincond - 6);
					temp.replace(ijoincond, ijoincond + 20, joinCond);
					if (irange >= 0) {
						temp.insert(irange, " ( ");
					}
				} else if (count > 1) {
					while (temp.indexOf("$unitConditionVal") >= 0) {
						int ijoincond = temp.indexOf("$unitConditionVal");
						temp.replace(ijoincond, ijoincond + 17, inCond);
					}
				}
				int fsession = temp.indexOf("$sessionDzzId");
				if (fsession >= 0) {
					temp.replace(fsession, fsession + 13, "@sessionDzzId");
				}
				result = cellDataService.executeSql(temp.toString(), Sqls.callback.str(), params)
						.getString();
				//防止从表中取不出来数据，为NULL值时候，给他赋值0，因为NULL到前台之后，算字符串和数值相加影响计算公式，所以这做下处理
				if (null == result || "".equals(result)) {
					result = "0";
				}
				//System.out.println(result);
			} catch (Exception e) {
				logger.debug("error sql is " + cell.getSqlExp(), e);
			}
		}
		return result;
	}
	private static Pattern p = Pattern.compile("'[^']+'"); // 正则表达式
	private static Pattern np = Pattern.compile("in\\s*\\([^\\(\\)]*'[^\\(\\)]*\\)"); // 正则表达式

	private static StringBuilder convertQuerySql(String sql, Map<String, Object> params) {
		Matcher m = p.matcher(sql); // 操作的字符串
		Matcher nm = np.matcher(sql);
		List<Integer> result = new ArrayList<Integer>();
		while (nm.find()) {
			result.add(nm.start());
			result.add(nm.end());
		}

		StringBuffer sb = new StringBuffer();
		int i = 0;
		while (m.find()) {
			if(!m.group().equalsIgnoreCase("'yyyy/MM/dd'")
					&& !inMatcherRange(result, m.start())) {
				params.put(String.valueOf(i), m.group().substring(1, m.group().length() - 1));
				m.appendReplacement(sb, " @" + i + " ");
				i++;    //字符串出现次数
			}
		}
		m.appendTail(sb);//从截取点将后面的字符串接上
		return new StringBuilder(sb);
	}

	private static boolean inMatcherRange(List<Integer> m, int index) {
		for(int i=0; i < m.size() /2;i++) {
			if(index > m.get(i*2) && index < m.get(i*2+1)) {
				return true;
			}
		}
		return false;
	}


	public static void main(String[] args) {
		String sql = "select count(1) from v_dt_c10 where v_dt_c10.dqfzjd in ('1','2','3') and (v_dt_c10.dwlb = '13' or v_dt_c10.dwxzlb like '91%' ) and (v_dt_c10.jjfzsj >= to_date('2016/01/01','yyyy/MM/dd') and v_dt_c10.jjfzsj < to_date('2016/12/31','yyyy/MM/dd') ) and v_dt_c10.sqrdsj >= to_date('2016/01/01','yyyy/MM/dd') and v_dt_c10.sqrdsj <= to_date('2016/12/31','yyyy/MM/dd')  and v_dt_c10.szzb in $unitConditionVal ";
		System.out.println( convertQuerySql(sql, new HashMap<String, Object>()));

		String sql1 = "old route new route ";
		System.out.println( convertQuerySql(sql1, new HashMap<String, Object>()));
	}

	private String buildUnitQueryStr(List<String> unitIds) {
		StringBuilder sb = new StringBuilder("(");
		for (String unitId : unitIds) {
			sb.append("'").append(unitId).append("',");
		}
		return sb.substring(0, sb.length() - 1) + ")";
	}


}
